from flask import Blueprint, request, jsonify, current_app
import os
import json
from services.db_service import DBService
from services.auth_service import auth_required
from utils.db import get_db
import psycopg2.extras
import re

db_bp = Blueprint('db', __name__, url_prefix='/api/db')

def get_db_credentials(task_id=None):
    """从项目表获取数据库连接凭证"""
    try:
        current_app.logger.info(f"尝试获取数据库连接凭证: task_id={task_id}")
        conn = get_db(current_app)
        cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        
        # 根据任务ID查询项目信息
        query = """
            SELECT 
                p.id,
                p.name,
                p.db_type,
                p.db_host as host,
                p.db_port as port,
                p.db_name as database,
                p.db_username as username,
                p.db_password as password,
                p.db_schema as schema
            FROM projects p
            JOIN main_tasks mt ON p.id = mt.project_id
            WHERE mt.id = %s AND p.status = 'active'
        """
        cursor.execute(query, (task_id,))
        project = cursor.fetchone()
        cursor.close()
        
        if project:
            current_app.logger.info(f"找到项目数据库配置: ID={project['id']}")
            
            credentials = {
                'id': project['id'],
                'name': project['name'],
                'type': project['db_type'],
                'host': project['host'],
                'port': project['port'],
                'database': project['database'],
                'username': project['username'],
                'password': project['password'],
                'schema': project['schema']
            }
            
            return credentials
        else:
            current_app.logger.warning(f"未找到匹配的项目数据库配置")
            return None
    except Exception as e:
        current_app.logger.error(f"获取数据库连接凭证失败: {str(e)}")
        import traceback
        current_app.logger.error(traceback.format_exc())
        return None

@db_bp.route('/status', methods=['POST'])
@auth_required
def db_status():
    """获取数据库连接状态"""
    try:
        data = request.get_json()
        task_id = data.get('task_id')
        
        if not task_id:
            return jsonify({"success": False, "message": "任务ID不能为空", "data": None}), 400
        
        # 获取连接凭证
        credentials = get_db_credentials(task_id)
        
        if not credentials:
            return jsonify({"success": False, "message": "找不到项目数据库配置信息", "data": None}), 404
        
        result = DBService.get_connection_status(credentials)
        return jsonify(result)
    except Exception as e:
        current_app.logger.error(f"获取数据库状态失败: {str(e)}")
        return jsonify({"success": False, "message": f"获取数据库状态失败: {str(e)}", "data": None}), 500

@db_bp.route('/query', methods=['POST'])
@auth_required
def db_query():
    """执行SQL查询"""
    try:
        data = request.get_json()
        task_id = data.get('task_id')
        query = data.get('query')
        
        if not task_id:
            return jsonify({"success": False, "message": "任务ID不能为空", "data": None}), 400
        
        if not query:
            return jsonify({"success": False, "message": "查询语句不能为空", "data": None}), 400
        
        # 获取连接凭证
        credentials = get_db_credentials(task_id)
        
        if not credentials:
            return jsonify({"success": False, "message": "找不到项目数据库配置信息", "data": None}), 404
        
        result = DBService.execute_query(credentials, query)
        
        # 记录操作到执行历史
        if result.get('success'):
            try:
                cursor = get_db(current_app).cursor()
                
                cursor.execute(
                    """
                    INSERT INTO task_executions (task_id, action, description, status, result, executed_by) 
                    VALUES (%s, %s, %s, %s, %s, %s)
                    """,
                    (
                        task_id,
                        'db_query',
                        query[:200],  # 截取前200个字符作为描述
                        'SUCCESS' if result.get('success') else 'FAILED',
                        json.dumps(result),
                        request.user.get('username') if hasattr(request, 'user') else 'system'
                    )
                )
                
                get_db(current_app).commit()
                cursor.close()
            except Exception as e:
                current_app.logger.error(f"记录数据库查询到执行历史失败: {str(e)}")
        
        return jsonify(result)
    except Exception as e:
        current_app.logger.error(f"执行SQL查询失败: {str(e)}")
        return jsonify({"success": False, "message": f"执行SQL查询失败: {str(e)}", "data": None}), 500

@db_bp.route('/table-structure', methods=['POST'])
@auth_required
def db_table_structure():
    """获取数据库表结构"""
    try:
        data = request.get_json()
        task_id = data.get('task_id')
        table_name = data.get('table_name')
        
        if not task_id:
            return jsonify({"success": False, "message": "任务ID不能为空", "data": None}), 400
        
        if not table_name:
            return jsonify({"success": False, "message": "表名不能为空", "data": None}), 400
        
        # 获取连接凭证
        credentials = get_db_credentials(task_id)
        
        if not credentials:
            return jsonify({"success": False, "message": "找不到项目数据库配置信息", "data": None}), 404
        
        result = DBService.get_table_structure(credentials, table_name)
        return jsonify(result)
    except Exception as e:
        current_app.logger.error(f"获取表结构失败: {str(e)}")
        return jsonify({"success": False, "message": f"获取表结构失败: {str(e)}", "data": None}), 500

@db_bp.route('/tables', methods=['POST'])
@auth_required
def db_tables():
    """获取数据库表列表"""
    try:
        data = request.get_json()
        task_id = data.get('task_id')
        
        if not task_id:
            return jsonify({"success": False, "message": "任务ID不能为空", "data": None}), 400
        
        # 获取连接凭证
        credentials = get_db_credentials(task_id)
        
        if not credentials:
            return jsonify({"success": False, "message": "找不到项目数据库配置信息", "data": None}), 404
        
        result = DBService.get_tables(credentials)
        return jsonify(result)
    except Exception as e:
        current_app.logger.error(f"获取表列表失败: {str(e)}")
        return jsonify({"success": False, "message": f"获取表列表失败: {str(e)}", "data": None}), 500

@db_bp.route('/update', methods=['POST'])
@auth_required
def db_update():
    """执行SQL更新语句"""
    try:
        data = request.get_json()
        task_id = data.get('task_id')
        sql = data.get('sql')
        
        if not task_id:
            return jsonify({"success": False, "message": "任务ID不能为空", "data": None}), 400
        
        if not sql:
            return jsonify({"success": False, "message": "SQL语句不能为空", "data": None}), 400
        
        # 获取连接凭证
        credentials = get_db_credentials(task_id)
        
        if not credentials:
            return jsonify({"success": False, "message": "找不到项目数据库配置信息", "data": None}), 404
        
        # 检查SQL语句类型，防止传入查询语句
        if sql.strip().lower().startswith('select'):
            return jsonify({"success": False, "message": "请使用/query接口执行查询语句", "data": None}), 400
        
        result = DBService.execute_update(credentials, sql)
        
        # 记录操作到执行历史
        if result.get('success'):
            try:
                cursor = get_db(current_app).cursor()
                
                cursor.execute(
                    """
                    INSERT INTO task_executions (task_id, action, description, status, result, executed_by) 
                    VALUES (%s, %s, %s, %s, %s, %s)
                    """,
                    (
                        task_id,
                        'db_update',
                        sql[:200],  # 截取前200个字符作为描述
                        'SUCCESS' if result.get('success') else 'FAILED',
                        json.dumps(result),
                        request.user.get('username') if hasattr(request, 'user') else 'system'
                    )
                )
                
                get_db(current_app).commit()
                cursor.close()
            except Exception as e:
                current_app.logger.error(f"记录数据库更新到执行历史失败: {str(e)}")
        
        return jsonify(result)
    except Exception as e:
        current_app.logger.error(f"执行SQL更新失败: {str(e)}")
        return jsonify({"success": False, "message": f"执行SQL更新失败: {str(e)}", "data": None}), 500

@db_bp.route('/create-table', methods=['POST'])
@auth_required
def db_create_table():
    """创建数据库表"""
    try:
        data = request.get_json()
        task_id = data.get('task_id')
        connection_id = data.get('connection_id')
        create_table_sql = data.get('create_table_sql')
        project_id = data.get('project_id')

        # 优先用task_id查找连接信息
        if task_id:
            credentials = get_db_credentials(task_id)
        elif connection_id:
            credentials = get_db_credentials(connection_id)
        else:
            return jsonify({"success": False, "message": "任务ID或连接ID不能为空", "data": None}), 400

        if not create_table_sql:
            return jsonify({"success": False, "message": "创建表SQL语句不能为空", "data": None}), 400

        # 检查SQL语句是否是CREATE TABLE语句
        if not create_table_sql.strip().lower().startswith('create table'):
            return jsonify({"success": False, "message": "SQL语句必须是CREATE TABLE语句", "data": None}), 400

        if not credentials:
            return jsonify({"success": False, "message": "找不到数据库连接信息", "data": None}), 404

        result = DBService.execute_update(credentials, create_table_sql)

        # 记录操作到执行历史
        if task_id:
            try:
                cursor = get_db(current_app).cursor()
                cursor.execute(
                    """
                    INSERT INTO task_executions (task_id, action, description, status, result, executed_by) 
                    VALUES (%s, %s, %s, %s, %s, %s)
                    """,
                    (
                        task_id,
                        'db_create',
                        create_table_sql[:200],
                        'SUCCESS' if result.get('success') else 'FAILED',
                        json.dumps(result),
                        request.user.get('username') if hasattr(request, 'user') else 'system'
                    )
                )
                get_db(current_app).commit()
                cursor.close()
            except Exception as e:
                current_app.logger.error(f"记录创建表到执行历史失败: {str(e)}")

        return jsonify(result)
    except Exception as e:
        current_app.logger.error(f"创建表失败: {str(e)}")
        return jsonify({"success": False, "message": f"创建表失败: {str(e)}", "data": None}), 500

@db_bp.route('/connections/<int:project_id>', methods=['GET'])
@auth_required
def get_project_connections(project_id):
    """获取项目数据库连接列表"""
    try:
        if not project_id:
            return jsonify({"success": False, "message": "项目ID不能为空", "data": None}), 400
        
        conn = get_db(current_app)
        cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        
        cursor.execute(
            """
            SELECT id, name, type, host, port, database, username
            FROM database_connections
            WHERE project_id = %s
            ORDER BY name
            """,
            (project_id,)
        )
        
        connections = cursor.fetchall()
        cursor.close()
        
        return jsonify({
            "success": True,
            "connections": connections
        })
    except Exception as e:
        current_app.logger.error(f"获取项目数据库连接列表失败: {str(e)}")
        return jsonify({"success": False, "message": f"获取项目数据库连接列表失败: {str(e)}", "data": None}), 500

@db_bp.route('/connections', methods=['POST'])
@auth_required
def create_connection():
    """创建数据库连接配置"""
    try:
        data = request.get_json()
        connection_data = data.get('connection_data')
        project_id = data.get('project_id')
        
        if not connection_data:
            return jsonify({"success": False, "message": "连接数据不能为空", "data": None}), 400
        
        if not project_id:
            return jsonify({"success": False, "message": "项目ID不能为空", "data": None}), 400
            
        required_fields = ['name', 'type', 'database']
        
        if connection_data.get('type') != 'sqlite':
            required_fields.extend(['host', 'port', 'username'])
        else:
            required_fields.append('file_path')
            
        for field in required_fields:
            if not connection_data.get(field):
                return jsonify({"success": False, "message": f"字段 {field} 不能为空", "data": None}), 400
        
        conn = get_db(current_app)
        cursor = conn.cursor()
        
        # 检查连接名称是否已存在
        cursor.execute(
            """
            SELECT COUNT(*) FROM database_connections
            WHERE project_id = %s AND name = %s
            """,
            (project_id, connection_data.get('name'))
        )
        
        count = cursor.fetchone()[0]
        
        if count > 0:
            return jsonify({"success": False, "message": "连接名称已存在", "data": None}), 400
        
        # 插入连接数据
        if connection_data.get('type') == 'sqlite':
            cursor.execute(
                """
                INSERT INTO database_connections 
                (project_id, name, type, database, file_path) 
                VALUES (%s, %s, %s, %s, %s)
                RETURNING id
                """,
                (
                    project_id,
                    connection_data.get('name'),
                    connection_data.get('type'),
                    connection_data.get('database'),
                    connection_data.get('file_path')
                )
            )
        else:
            cursor.execute(
                """
                INSERT INTO database_connections 
                (project_id, name, type, host, port, database, username, password) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                RETURNING id
                """,
                (
                    project_id,
                    connection_data.get('name'),
                    connection_data.get('type'),
                    connection_data.get('host'),
                    connection_data.get('port'),
                    connection_data.get('database'),
                    connection_data.get('username'),
                    connection_data.get('password')
                )
            )
        
        connection_id = cursor.fetchone()[0]
        conn.commit()
        
        return jsonify({
            "success": True,
            "message": "数据库连接创建成功",
            "connection_id": connection_id
        })
    except Exception as e:
        current_app.logger.error(f"创建数据库连接失败: {str(e)}")
        return jsonify({"success": False, "message": f"创建数据库连接失败: {str(e)}", "data": None}), 500

@db_bp.route('/test-connection', methods=['POST'])
@auth_required
def test_connection():
    """测试数据库连接"""
    try:
        data = request.get_json()
        connection_data = data.get('connection_data')
        
        if not connection_data:
            return jsonify({"success": False, "message": "连接数据不能为空", "data": None}), 400
            
        required_fields = ['type', 'database']
        
        if connection_data.get('type') != 'sqlite':
            required_fields.extend(['host', 'port', 'username'])
        else:
            required_fields.append('file_path')
            
        for field in required_fields:
            if not connection_data.get(field):
                return jsonify({"success": False, "message": f"字段 {field} 不能为空", "data": None}), 400
        
        result = DBService.test_connection(connection_data)
        return jsonify(result)
    except Exception as e:
        current_app.logger.error(f"测试数据库连接失败: {str(e)}")
        return jsonify({"success": False, "message": f"测试数据库连接失败: {str(e)}", "data": None}), 500 